package com.pokeoffice.database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Vector;

import com.pokeoffice.builder.TreatmentBuilder;
import com.pokeoffice.builder.UserBuilder;
import com.pokeoffice.builder.VisitBuilder;
import com.pokeoffice.calendar.Visit;
import com.pokeoffice.medical.Treatment;
import com.pokeoffice.users.User;
import com.pokeoffice.users.UserType;

/**
 * The Class Control.
 * 
 * @author Kyle Davis
 * 
 *         (Pattern: Singleton) Executes commands on the database
 */
public class Control {

	/** The ctrl. */
	private static Control ctrl = null;

	/** The MA x_ logi n_ attempts. */
	public final int MAX_LOGIN_ATTEMPTS = 3;

	/** The MYSQ l_ port. */
	private final int MYSQL_PORT = 3306;

	/** The MYSQ l_ host. */
	private final String MYSQL_HOST = "localhost";

	/** The DATABASE. */
	private final String DATABASE = "pokeoffice";

	/** The connection. */
	public final String connection = "jdbc:mysql://" + MYSQL_HOST + ":"
			+ MYSQL_PORT + "/" + DATABASE;

	/** The create treatment entry. */
	public final String createTreatmentEntry = "INSERT INTO treatment (user_id, prof_id"
			+ ", nurse_id, visit_id, pokemon_id, treatment_time, prof_orders, symptoms, "
			+ "vitals, diagnosis) VALUES(?,?,?,?,?,?,?,?,?,?);";

	/** The create user entry. */
	public final String createUserEntry = "INSERT INTO user (access, name, birth_date, "
			+ "gender, phone, city, region, pharmacy, insurance) VALUES(?,?,?,?,"
			+ "?,?,?,?,?);";

	// TODO ? always has '' grants shouldn't be in them.
	/** The grant. */
	public final String grant = "GRANT ";

	/** The create user grants. */
	public final String createUserGrants = " ON *.* TO ?@'" + MYSQL_HOST
			+ "';";

	/** The create admin grants. */
	public final String createAdminGrants = " ON *.* TO ?@'" + MYSQL_HOST
			+ "' WITH GRANT OPTION;";

	/** The create user login. */
	public final String createUserLogin = "CREATE USER ?@'" + MYSQL_HOST
			+ "' IDENTIFIED BY ?;";

	/** The create visit entry. */
	public final String createVisitEntry = "INSERT INTO visit (user_id, prof_id, nurse_"
			+ "id, treatment_id, visit_time, visit_location) VALUES(?,?,?,?,?,"
			+ "?);";

	/** The delete treatment entry. */
	public final String deleteTreatmentEntry = "DELETE FROM treatment WHERE id=?;";

	/** The delete user entry. */
	public final String deleteUserEntry = "DELETE FROM user WHERE id=?;";

	/** The delete visit entry. */
	public final String deleteVisitEntry = "DELETE FROM visit WHERE id=?;";

	/** The drop user login. */
	public final String dropUserLogin = "DROP USER ?@'" + MYSQL_HOST + "';";

	/** The login attempts. */
	public final String loginAttempts = "SELECT login_attempt FROM user WHERE id=?;";

	/** The read treatment entry. */
	public final String readTreatmentEntry = "SELECT * FROM treatment WHERE id=?;";

	/** The read user entry. */
	public final String readUserEntry = "SELECT * FROM user WHERE id=?;";

	/** The read visit entry. */
	public final String readVisitEntry = "SELECT * FROM visit WHERE user_id=? ORDER BY visit_time;";

	/** The revoke all privileges. */
	public final String revokeAllPrivileges = "REVOKE ALL PRIVILEGES, GRANT OPTION FROM"
			+ " ?@'" + MYSQL_HOST + "';";

	/** The update login attempts. */
	public final String updateLoginAttempts = "UPDATE user SET login_attempt=? WHERE "
			+ "id=?;";

	/** The update treatment entry. */
	public final String updateTreatmentEntry = "UPDATE treatment SET 'user_id'=?, 'pr"
			+ "of_id'=?, 'nurse_id'=?, 'visit_id'=?, 'pokemon_id'=?, 'treatment_"
			+ "time'=?, 'prof_orders'=?, 'symptoms'=?, 'vitals'=?, 'diagnosis'='"
			+ "?';";

	/*
	 * Design Decision: You can NOT change a user's name. This would cause
	 * issues with the databse logins. Possibly implement later?
	 */
	/** The update user entry. */
	public final String updateUserEntry = "UPDATE user SET 'access'=?, 'birth_date'='"
			+ "?', 'gender'=?, 'phone'=?, 'city'=?, 'region'=?, 'pharmacy'=?, "
			+ "'insurance'=?;";

	/** The update visit entry. */
	public final String updateVisitEntry = "UPDATE visit SET 'user_id'=?, 'prof_id'='"
			+ "?', 'nurse_id'=?, 'treatment_id'=?, 'visit_time'=?, 'visit_location"
			+ "'=?;";

	/** The login map. */
	private Map<Long, Integer> loginMap;

	/**
	 * Instantiates a new control.
	 */
	private Control() {
		loginMap = new HashMap<Long, Integer>();
	}

	// Blocking methods
	/**
	 * Block.
	 *
	 * @param id the id
	 */
	public void block(long id) {
		if (loginMap.containsKey(id)) {
			loginMap.put(id, loginMap.remove(id) + 1);
		} else {
			loginMap.put(id, 1);
		}
	}

	/**
	 * Checks if is blocked.
	 *
	 * @param id the id
	 * @return true, if is blocked
	 */
	public boolean isBlocked(long id) {
		if (loginMap.containsKey(id)) {
			return (loginMap.get(id) >= 3);
		}

		return false;
	}

	/**
	 * Reset block.
	 *
	 * @param id the id
	 */
	public void resetBlock(long id) {
		loginMap.remove(id);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see java.lang.Object#clone()
	 */
	public Object clone() throws CloneNotSupportedException {
		throw new CloneNotSupportedException();
	}

	/**
	 * Connect.
	 * 
	 * @param user
	 *            the user
	 * @param pass
	 *            the pass
	 * @return the connection
	 */
	public Connection connect(String user, String pass) {
		Connection con = null;

		try {
			con = DriverManager.getConnection(connection, user, pass);
			con.setAutoCommit(false);
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return con;
	}

	/**
	 * Creates the treatment.
	 * 
	 * @param con
	 *            the con
	 * @param tre
	 *            the tre
	 * @return the long
	 */
	public long createTreatment(Connection con, Treatment tre) {
		long treatmentID = 0;

		try {
			PreparedStatement ps = con.prepareStatement(createTreatmentEntry,
					Statement.RETURN_GENERATED_KEYS);

			ps.setLong(1, tre.getUserID());
			ps.setLong(2, tre.getProfessorID());
			ps.setLong(3, tre.getNurseID());
			ps.setLong(4, tre.getVisitID());
			ps.setLong(5, tre.getPokemonID());
			ps.setString(6, tre.getTime());
			ps.setString(7, tre.getOrders());
			ps.setString(8, tre.getSymptoms());
			ps.setString(9, tre.getVitals());
			ps.setString(10, tre.getDiagnosis());
			ps.execute();

			ResultSet rs = ps.getGeneratedKeys();
			if (rs.next()) {
				treatmentID = rs.getLong("id");
			}

			tre.setID(treatmentID);
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return treatmentID;
	}

	/**
	 * Creates the user.
	 * 
	 * @param con
	 *            the con
	 * @param use
	 *            the use
	 * @return the long
	 * @throws SQLException
	 *             the sQL exception
	 */
	public long createUser(Connection con, User use) throws SQLException {
		long userID = 0;

		try {
			PreparedStatement ps = con.prepareStatement(createUserEntry,
					Statement.RETURN_GENERATED_KEYS);

			ps.setString(1, "" + use.getUserType());
			ps.setString(2, use.getName());
			ps.setString(3, use.getBirthdate());
			ps.setString(4, use.getGender());
			ps.setString(5, use.getPhone());
			ps.setString(6, use.getCity());
			ps.setString(7, use.getRegion());
			ps.setString(8, use.getPharmacy());
			ps.setString(9, use.getInsurance());
			ps.execute();

			ResultSet rs = ps.getGeneratedKeys();
			if (rs.next()) {
				userID = rs.getLong(1);
			}

			use.setID(userID);

			rs.close();
			ps.close();

			ps = con.prepareStatement(createUserLogin);
			ps.setString(1, use.getLogin());
			ps.setString(2, use.getLogin());

			ps.execute();
			ps.close();

			if (use.getUserType() == UserType.ADMIN) {
				ps = con.prepareStatement(grant + use.grants()
						+ createAdminGrants);
			} else {
				ps = con.prepareStatement(grant + use.grants()
						+ createUserGrants);
			}

			ps.setString(1, use.getLogin());

			ps.execute();
			ps.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return userID;
	}

	/**
	 * Creates the visit.
	 * 
	 * @param con
	 *            the con
	 * @param vis
	 *            the vis
	 * @return the long
	 */
	public long createVisit(Connection con, Visit vis) {
		long visitID = 0;

		try {
			PreparedStatement ps = con.prepareStatement(createVisitEntry,
					Statement.RETURN_GENERATED_KEYS);

			ps.setLong(1, vis.getUserID());
			ps.setLong(2, vis.getProfessorID());
			ps.setLong(3, vis.getNurseID());
			ps.setLong(4, vis.getTreatmentID());
			ps.setString(5, vis.getVisitTime());
			ps.setString(6, vis.getVisitLocation());
			ps.execute();

			ResultSet rs = ps.getGeneratedKeys();

			if (rs.next()) {
				visitID = rs.getLong(1);
			}

			vis.setID(visitID);
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return visitID;
	}

	/**
	 * Delete treatment.
	 * 
	 * @param con
	 *            the con
	 * @param tid
	 *            the tid
	 * @return the treatment
	 */
	public Treatment deleteTreatment(Connection con, long tid) {
		Treatment tre = null;

		try {
			PreparedStatement ps = con.prepareStatement(deleteTreatmentEntry,
					Statement.RETURN_GENERATED_KEYS);

			ps.setLong(1, tid);
			ps.execute();

			ResultSet rs = ps.getGeneratedKeys();
			tre = TreatmentBuilder.buildFromResultSet(rs);

			rs.close();
			ps.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return tre;
	}

	/**
	 * Delete user.
	 * 
	 * @param con
	 *            the con
	 * @param uid
	 *            the uid
	 * @return the user
	 */
	public User deleteUser(Connection con, long uid) {
		User use = null;

		try {
			PreparedStatement ps = con.prepareStatement(deleteUserEntry,
					Statement.RETURN_GENERATED_KEYS);

			ps.setLong(1, uid);
			ps.execute();

			ResultSet rs = ps.getGeneratedKeys();
			use = UserBuilder.buildFromResultSet(rs);

			rs.close();
			ps.close();

			ps = con.prepareStatement(revokeAllPrivileges);
			ps.setString(1, use.getLogin());
			ps.execute();
			ps.close();

			ps = con.prepareStatement(dropUserLogin);
			ps.setString(1, use.getLogin());
			ps.execute();
			ps.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}

		// TODO Delete users data? Treatments, visits etc? OR back it up?

		return use;
	}

	/**
	 * Delete visit.
	 * 
	 * @param con
	 *            the con
	 * @param vid
	 *            the vid
	 * @return the visit
	 */
	public Visit deleteVisit(Connection con, long vid) {
		Visit vis = null;

		try {
			PreparedStatement ps = con.prepareStatement(deleteVisitEntry,
					Statement.RETURN_GENERATED_KEYS);

			ps.setLong(1, vid);
			ps.execute();

			ResultSet rs = ps.getGeneratedKeys();
			vis = VisitBuilder.buildOneFromResultSet(rs);

			rs.close();
			ps.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return vis;
	}

	/**
	 * Gets the.
	 * 
	 * @return the control
	 */
	public static synchronized Control get() {
		if (ctrl == null) {
			ctrl = new Control();
		}

		return ctrl;
	}

	/**
	 * Increment login attempts.
	 * 
	 * @param userID
	 *            the user id
	 */
	public void incrementLoginAttempts(long userID) {
		int attempt = getLoginAttempt(userID) + 1;
		setLoginAttempts(userID, attempt);
	}

	/**
	 * Gets the login attempt.
	 * 
	 * @param userID
	 *            the user id
	 * @return the login attempt
	 */
	public int getLoginAttempt(long userID) {
		int attempt = 0;

		try {
			Connection con = view();
			PreparedStatement ps = con.prepareStatement(loginAttempts);
			ps.setLong(1, userID);

			ResultSet results = ps.executeQuery();

			if (results.next()) {
				attempt = results.getInt("login_attempt");
			}

			results.close();
			ps.close();
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return attempt;
	}

	/**
	 * Read treatment.
	 * 
	 * @param con
	 *            the con
	 * @param tid
	 *            the tid
	 * @return the treatment
	 */
	public Treatment readTreatment(Connection con, long tid) {
		Treatment tre = null;

		try {
			PreparedStatement ps = con.prepareStatement(readTreatmentEntry,
					Statement.RETURN_GENERATED_KEYS);

			ps.setLong(1, tid);
			ps.execute();

			ResultSet rs = ps.getGeneratedKeys();
			tre = TreatmentBuilder.buildFromResultSet(rs);

			rs.close();
			ps.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return tre;
	}

	/**
	 * Read user.
	 * 
	 * @param con
	 *            the con
	 * @param uid
	 *            the uid
	 * @return the user
	 */
	public User readUser(Connection con, long uid) {
		User use = null;

		try {
			PreparedStatement ps = con.prepareStatement(readUserEntry,
					Statement.RETURN_GENERATED_KEYS);

			ps.setLong(1, uid);

			ResultSet rs = ps.executeQuery();
			use = UserBuilder.buildFromResultSet(rs);

			rs.close();
			ps.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return use;
	}

	/**
	 * Read visit.
	 * 
	 * @param con
	 *            the con
	 * @param uid
	 *            the vid
	 * @return the visit
	 */
	public Vector<Visit> readVisits(Connection con, long uid) {
		Vector<Visit> vis = null;

		try {
			PreparedStatement ps = con.prepareStatement(readVisitEntry,
					Statement.RETURN_GENERATED_KEYS);

			ps.setLong(1, uid);

			ResultSet rs = ps.executeQuery();
			vis = VisitBuilder.buildFromResultSet(rs);

			rs.close();
			ps.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return vis;
	}

	/**
	 * Reset login attempts.
	 * 
	 * @param userID
	 *            the user id
	 */
	public void resetLoginAttempts(long userID) {
		setLoginAttempts(userID, 0);
	}

	/**
	 * Sets the login attempts.
	 * 
	 * @param userID
	 *            the user id
	 * @param attempt
	 *            the attempt
	 */
	public void setLoginAttempts(long userID, int attempt) {
		try {
			attempt = Math.min(attempt, MAX_LOGIN_ATTEMPTS);

			Connection con = view();

			PreparedStatement ps = con.prepareStatement(updateLoginAttempts);
			ps.setInt(1, attempt);
			ps.setLong(2, userID);
			ps.execute();

			con.commit();

			ps.close();
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see java.lang.Object#toString()
	 */
	public String toString() {
		return "Control";
	}

	/**
	 * Update treatment.
	 * 
	 * @param con
	 *            the con
	 * @param tid
	 *            the tid
	 * @param update
	 *            the update
	 * @return the treatment
	 */
	public Treatment updateTreatment(Connection con, long tid, Treatment update) {
		Treatment tre = null;

		try {
			PreparedStatement ps = con.prepareStatement(updateTreatmentEntry,
					Statement.RETURN_GENERATED_KEYS);

			ps.setLong(1, update.getUserID());
			ps.setLong(2, update.getProfessorID());
			ps.setLong(3, update.getNurseID());
			ps.setLong(4, update.getVisitID());
			ps.setLong(5, update.getPokemonID());
			ps.setString(6, update.getTime());
			ps.setString(7, update.getOrders());
			ps.setString(8, update.getSymptoms());
			ps.setString(9, update.getVitals());
			ps.setString(10, update.getDiagnosis());
			ps.execute();

			ResultSet rs = ps.getGeneratedKeys();
			tre = TreatmentBuilder.buildFromResultSet(rs);

			rs.close();
			ps.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return tre;
	}

	/**
	 * Update user.
	 * 
	 * @param con
	 *            the con
	 * @param uid
	 *            the uid
	 * @param update
	 *            the update
	 * @return the user
	 */
	public User updateUser(Connection con, long uid, User update) {
		User use = null;

		try {
			PreparedStatement ps = con.prepareStatement(updateUserEntry,
					Statement.RETURN_GENERATED_KEYS);

			ps.setString(1, "" + update.getUserType());
			ps.setString(2, update.getBirthdate());
			ps.setString(3, update.getGender());
			ps.setString(4, update.getPhone());
			ps.setString(5, update.getCity());
			ps.setString(6, update.getRegion());
			ps.setString(7, update.getPharmacy());
			ps.setString(8, update.getInsurance());
			ps.execute();

			ResultSet rs = ps.getGeneratedKeys();
			use = UserBuilder.buildFromResultSet(rs);

			rs.close();
			ps.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return use;
	}

	/**
	 * Update visit.
	 * 
	 * @param con
	 *            the con
	 * @param vid
	 *            the vid
	 * @param update
	 *            the update
	 * @return the visit
	 */
	public Visit updateVisit(Connection con, long vid, Visit update) {
		Visit vis = null;

		try {
			PreparedStatement ps = con.prepareStatement(updateVisitEntry,
					Statement.RETURN_GENERATED_KEYS);

			ps.setLong(1, update.getUserID());
			ps.setLong(2, update.getProfessorID());
			ps.setLong(3, update.getNurseID());
			ps.setLong(4, update.getTreatmentID());
			ps.setString(5, update.getVisitTime());
			ps.setString(6, update.getVisitLocation());
			ps.execute();

			ResultSet rs = ps.getGeneratedKeys();
			vis = VisitBuilder.buildOneFromResultSet(rs);

			rs.close();
			ps.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return vis;
	}

	/**
	 * View.
	 * 
	 * @return the connection
	 */
	public Connection view() {
		Connection con = null;

		try {
			con = connect("view", "view");
			con.setAutoCommit(false);
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return con;
	}
}
